﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;

namespace PalBattleEmulator.Template.Tools
{
    public static class ExcelTemplateTools
    {
        /// <summary>
        /// 从excel中读取模板
        /// </summary>
        /// <param name="path"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static List<Dictionary<string, string>> LoadExcelFile(string path, string sheetName)
        {
            return LoadExcelFileUsingDatabase(path, sheetName);
        }

        public static void GenerateExcelFile(List<Dictionary<string, string>> data, string path, string sheetName)
        {
            GenerateExcelFileUsingDatabase(data, path, sheetName);
        }
        
        /// <summary>
        /// 直接读取excel
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private static List<Dictionary<string, string>> LoadExcelFileUsingWorkbook(string path, string sheetName)
        {
            Excel.Application excel = new Excel.Application();
            try
            {
                excel.UserControl = true;
                excel.DisplayAlerts = false;

                object Nothing = System.Reflection.Missing.Value;
                excel.Application.Workbooks.Open(path, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing,
                    Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
                Excel.Workbook wb = excel.Workbooks[1];
                Excel.Worksheet ws = (Excel.Worksheet)wb.Sheets[sheetName];
                int rowCount = ws.UsedRange.Rows.Count;
                int colCount = ws.UsedRange.Columns.Count;

                //先读列名
                string[] titles = new string[colCount + 1];
                for (int i = 1; i <= colCount; i++)
                {
                    Excel.Range range = (Excel.Range)ws.Cells[1, i];
                    titles[i] = range.Text.ToString();
                }

                //再读数据
                List<Dictionary<string, string>> objList = new List<Dictionary<string, string>>();

                int rowIndex = 2;
                while (rowIndex <= rowCount)
                {
                    Dictionary<string, string> dic = new Dictionary<string, string>();
                    for (int i = 1; i <= colCount; i++)
                    {
                        Excel.Range range = (Excel.Range)ws.Cells[rowIndex, i];
                        dic.Add(titles[i], range.Text.ToString());
                    }
                    rowIndex++;
                    objList.Add(dic);
                }

                excel.Quit();
                return objList;
            }
            catch
            {
                excel.Quit();
                return null;
            }
           

            //for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++)
            //{
            //    int colIndex = 0;
            //    while (colIndex < colCount)
            //    {
            //        Excel.Range range = (Excel.Range)ws.Cells[rowIndex, ++colIndex];
            //        Console.Write(range.Text);
            //        Console.Write(",");
            //    }
            //    Console.WriteLine();
                
            //}
            //return null;

        }
        
        /// <summary>
        /// 通过OLEDB数据库连接方式读取excel
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private static List<Dictionary<string, string>> LoadExcelFileUsingDatabase(string path, string sheetName)
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";Extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strConn);
                OleDbCommand command = new OleDbCommand(String.Format("SELECT * FROM [{0}$]", sheetName), conn);

                conn.Open();
                OleDbDataReader reader = command.ExecuteReader();

                //先读列名
                int count = reader.FieldCount;
                string[] titles = new string[count];
                for (int i = 1; i < count; i++)
                {
                    titles[i] = reader.GetName(i);
                }

                //读数据
                List<Dictionary<string, string>> objList = new List<Dictionary<string, string>>();

                while (reader.Read())
                {
                    Dictionary<string, string> dic = new Dictionary<string, string>();
                    for (int i = 1; i < count; i++)
                    {
                        dic.Add(titles[i], reader[i].ToString());
                    }
                    objList.Add(dic);
                }
                reader.Close();
                return objList;
            }
            catch (System.Data.OleDb.OleDbException ex)
            {
                System.Diagnostics.Debug.WriteLine("读取Excel发生错误：" + ex.Message);
                return null;
            }
            
        }

        private static void GenerateExcelFileUsingWorkbook(string path, string sheetName)
        {
            Excel.ApplicationClass oExcel;
            oExcel = new Excel.ApplicationClass();
            oExcel.UserControl = false;
            object Nothing = System.Reflection.Missing.Value;
            Excel.WorkbookClass wb = (Excel.WorkbookClass)oExcel.Workbooks.Add(Nothing);
            wb.Worksheets.Add(Nothing, Nothing, 1, Nothing);

            oExcel.Cells[1, 1] = "第一列";
            oExcel.Cells[1, 2] = "第二列";
            oExcel.Cells[1, 3] = "第三列";

            wb.Saved = true;
            oExcel.ActiveWorkbook.SaveCopyAs(path);
            oExcel.Quit();
            System.GC.Collect();

        }

        /// <summary>
        /// 通过oledb方式生成excel文件
        /// </summary>
        /// <param name="path"></param>
        /// <param name="sheetName"></param>
        private static void GenerateExcelFileUsingDatabase(List<Dictionary<string, string>> data, string path, string sheetName)
        {
            try
            {
                if (System.IO.File.Exists(path))
                    System.IO.File.Delete(path);

                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                //建表
                try
                {
                    cmd.CommandText = "CREATE TABLE " + sheetName + " (row int, id int, name text)";
                    cmd.ExecuteNonQuery();
                }
                catch (System.Data.OleDb.OleDbException)
                {
                    //表存在，清空数据
                    //cmd.CommandText = "DROP TABLE " + sheetName;
                    //cmd.ExecuteNonQuery();
                    //cmd.CommandText = "CREATE TABLE " + sheetName + " (row int, id int, name text)";
                    //cmd.ExecuteNonQuery();
                }
                //添加数据
                for (int i = 0; i < data.Count; i++)
                {
                    Dictionary<string, string> dic = data[i];
                    StringBuilder keys = new StringBuilder(" (row");
                    StringBuilder values = new StringBuilder(" VALUES('").Append(i + 1).Append("'");
                    foreach(string key in dic.Keys)
                    {
                        keys.Append(", ");
                        values.Append(", ");
                        keys.Append(key);
                        values.Append("'");
                        values.Append(dic[key]);
                        values.Append("'");
                    }
                    keys.Append(")");
                    values.Append(")");
                    cmd.CommandText = "INSERT INTO [" + sheetName + "$]" + keys.ToString() + values.ToString();
                    cmd.ExecuteNonQuery();
                }
                //cmd.CommandText = "INSERT INTO [" + sheetName + "$] (row, id, name) VALUES('0', '0', '李逍遥')";
                //cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (System.Data.OleDb.OleDbException ex)
            {
                System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);
            }
        
        }
    }
}
